/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.nxlv2.nomad.accessdb.dao.jdbc;

import com.nxlv2.nomad.accessdb.dao.DAOException;
import com.nxlv2.nomad.accessdb.dao.IUtilisateurDao;
import com.nxlv2.nomad.accessdb.dao.IncoherentResultException;
import com.nxlv2.nomad.accessdb.dao.NoResultException;
import com.nxlv2.nomad.businessobjects.Client;
import com.nxlv2.nomad.businessobjects.Satisfaction;
import com.nxlv2.nomad.businessobjects.TauxSatisfaction;
import com.nxlv2.nomad.businessobjects.Utilisateur;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 *
 * @author Nastasia
 */
public class JdbcUtilisateurDao implements IUtilisateurDao {

    private static final Logger logger = LoggerFactory.getLogger(JdbcUtilisateurDao.class);
    private static final ConnectionManager manager = new ConnectionManager();

    /**
     *
     * @param utilisateur
     * @throws DAOException
     */
    @Override
    public void saveUtilisateur(Utilisateur utilisateur) throws DAOException {
        if (null == utilisateur) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("INSERT INTO utilisateur "
                + "(idBase, nom, prenom, mail, mdp, dateCreation) ");
        builder.append("VALUES (?, AES_ENCRYPT(?,?),AES_ENCRYPT(?,?),"
                + "AES_ENCRYPT(?,?),AES_ENCRYPT(?,?), NOW() )");

        String request = builder.toString();
        logger.debug("Insert utilisateur: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request, PreparedStatement.RETURN_GENERATED_KEYS);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, utilisateur.getIdBase());
            ps.setString(2, utilisateur.getNom());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, utilisateur.getPrenom());
            ps.setString(5, manager.getKeyAES());
            ps.setString(6, utilisateur.getMail());
            ps.setString(7, manager.getKeyAES());
            ps.setString(8, utilisateur.getMdp());
            ps.setString(9, manager.getKeyAES());
            ps.executeUpdate();

        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }

        try {
            ResultSet rs = ps.getGeneratedKeys();

            int pk = 0;

            while (rs.next()) {
                pk = rs.getInt(1);
            }
            utilisateur.setIdAppli(pk);

        } catch (SQLException e) {
            logger.error("Can not execute insert in db.");
            throw new DAOException("Can not execute insert in db.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param id
     * @throws DAOException
     */
    @Override
    public void deleteUtilisateur(int id) throws DAOException {
        if (id < 1) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("DELETE FROM utilisateur WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Delete utilisateur: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, id);
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not delete utilisateur with id.", e);
            throw new DAOException("Can not delete utilisateur with id.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param utilisateur
     * @throws DAOException
     */
    @Override
    public void updateUtilisateur(Utilisateur utilisateur) throws DAOException {
        if (null == utilisateur) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();
        builder.append("UPDATE utilisateur ");
        builder.append("SET idBase = ?, nom = AES_ENCRYPT(?,?), prenom = AES_ENCRYPT(?,?), "
                + "mail = AES_ENCRYPT(?,?), mdp = AES_ENCRYPT(?,?), dateModification = NOW(), dateSynchro = ? ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Update utilisateur: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, utilisateur.getIdBase());
            ps.setString(2, utilisateur.getNom());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, utilisateur.getPrenom());
            ps.setString(5, manager.getKeyAES());
            ps.setString(6, utilisateur.getMail());
            ps.setString(7, manager.getKeyAES());
            ps.setString(8, utilisateur.getMdp());
            ps.setString(9, manager.getKeyAES());
            
            DateTime dt = new DateTime();
            DateTimeFormatter fmt = DateTimeFormat.forPattern("YYYY-MM-dd HH:mm:ss");
            
            ps.setString(10, fmt.print(utilisateur.getDateSynchro()));
            ps.setInt(11, utilisateur.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not populate request with data.");
            throw new DAOException("Can not populate request with data.", e);
        }
        manager.fermerConnection();
    }

    /**
     *
     * @param id
     * @return Utilisateur
     * @throws DAOException
     */
    @Override
    public Utilisateur findById(int id) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli, idBase, ");
        builder.append("AES_DECRYPT(nom,?) AS nom, AES_DECRYPT(prenom,?) AS prenom,"
                + " AES_DECRYPT(mail,?) AS mail, AES_DECRYPT(mdp,?) AS mdp, dateSynchro ");
        builder.append("FROM utilisateur ");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("select utilisateur: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, manager.getKeyAES());
            ps.setInt(5, id);
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Utilisateur> utilisateurs = null;
        try {
            utilisateurs = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == utilisateurs || utilisateurs.isEmpty()) {
            throw new NoResultException("No utilisateur found with id " + id);
        }
        if (utilisateurs.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }
        manager.fermerConnection();
        return utilisateurs.get(0);


    }

    /**
     *
     * @param email
     * @return Utilisateur
     * @throws DAOException
     */
    @Override
    public Utilisateur findByEmail(String email) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli, idBase, ");
        builder.append("AES_DECRYPT(nom,?) AS nom, AES_DECRYPT(prenom,?) AS prenom,"
                + " AES_DECRYPT(mail,?) AS mail, AES_DECRYPT(mdp,?) AS mdp, dateSynchro ");
        builder.append("FROM utilisateur ");
        builder.append("WHERE AES_DECRYPT(mail,?) = ?");

        String request = builder.toString();
        logger.debug("select utilisateur: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, manager.getKeyAES());
            ps.setString(5, manager.getKeyAES());
            ps.setString(6, email);
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with email.");
            throw new DAOException("Can not select data with email.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Utilisateur> utilisateurs = null;
        try {
            utilisateurs = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == utilisateurs || utilisateurs.isEmpty()) {
            throw new NoResultException("No utilisateur found with email " + email);
        }
        if (utilisateurs.size() > 1) {
            throw new IncoherentResultException("Many records with a same email.");
        }
        manager.fermerConnection();
        return utilisateurs.get(0);


    }

    /**
     *
     * @param rst
     * @return List<Utilisateur>
     * @throws SQLException
     */
    private List<Utilisateur> map(ResultSet rst) throws SQLException {
        List<Utilisateur> utilisateurs = new ArrayList<>();

        while (rst.next()) {

            Utilisateur utilisateur = new Utilisateur();
            utilisateur.setIdAppli(rst.getInt("idAppli"));
            utilisateur.setIdBase(rst.getInt("idBase"));
            utilisateur.setNom(rst.getString("nom"));
            utilisateur.setPrenom(rst.getString("prenom"));
            utilisateur.setMail(rst.getString("mail"));
            utilisateur.setMdp(rst.getString("mdp"));
            utilisateur.setDateSynchro(new DateTime((null == rst.getDate("dateSynchro")) ? "0" : rst.getDate("dateSynchro")));
            utilisateurs.add(utilisateur);
            logger.debug("map utilisateur: {}", utilisateur);
        }
        return utilisateurs;
    }

    /**
     *
     * @param utilisateur
     * @return bool
     * @throws DAOException
     */
    @Override
    public boolean verifierConnection(Utilisateur utilisateur) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT idAppli, idBase, ");
        builder.append("AES_DECRYPT(nom,?) AS nom, AES_DECRYPT(prenom,?) AS prenom,"
                + " AES_DECRYPT(mail,?) AS mail, AES_DECRYPT(mdp,?) AS mdp, dateSynchro ");
        builder.append("FROM utilisateur ");
        builder.append("WHERE AES_DECRYPT(mail,?) = ? "
                + " AND AES_DECRYPT(mdp,?) = ?");

        String request = builder.toString();
        logger.debug("select utilisateur: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setString(3, manager.getKeyAES());
            ps.setString(4, manager.getKeyAES());
            ps.setString(5, manager.getKeyAES());
            ps.setString(6, utilisateur.getMail());
            ps.setString(7, manager.getKeyAES());
            ps.setString(8, utilisateur.getMdp());

            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data.");
            throw new DAOException("Can not select data.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Utilisateur> utilisateurs = null;
        try {
            utilisateurs = map(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }
        manager.fermerConnection();
        if (1 == utilisateurs.size()) {
            return true;
        }
        if (null == utilisateurs || utilisateurs.isEmpty()) {
            return false;
        }
        if (utilisateurs.size() > 1) {
            throw new IncoherentResultException("Many records with a same id.");
        }

        return false;


    }

    /**
     *
     * @param utilisateur
     * @return List<Client>
     * @throws DAOException
     */
    @Override
    public List<Client> getClientsParUtilisateur(Utilisateur utilisateur) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT client.idBase, client.idAppli, ");
        builder.append("AES_DECRYPT(client.nom,?) AS nom ");
        builder.append("FROM client ");
        builder.append("WHERE client.idAppliUtilisateur = ?");

        String request = builder.toString();
        logger.debug("Select clients par utilisateur: {}", request);
        logger.debug("utilisateur {}", utilisateur);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setInt(2, utilisateur.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Client> clients = null;
        try {
            clients = mapClient(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == clients || clients.isEmpty()) {
//            throw new NoResultException("No client found with utilisateur : " + utilisateur );
        }
        manager.fermerConnection();
        return clients;
    }
    /**
     * 
     * @param utilisateur
     * @return
     * @throws DAOException 
     */
    public List<Satisfaction> getSatisfactionsParUtilisateur(Utilisateur utilisateur) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT s.idAppli, s.idBase, ");
        builder.append("AES_DECRYPT(tauxSatisfaction,?) AS tauxSatisfaction, ");
        builder.append("AES_DECRYPT(message,?) AS message ");
        builder.append("FROM satisfaction AS s ");
        builder.append("INNER JOIN commande ON idAppliCommande = commande.idAppli ");
        builder.append("INNER JOIN client ON idAppliClient = client.idAppli ");
        builder.append("WHERE idAppliUtilisateur = ?");

        String request = builder.toString();
        logger.debug("select satisfaction: {}", request);
        logger.debug("idUtilisater: {}", utilisateur.getIdAppli());

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }
        try {
            ps.setString(1, manager.getKeyAES());
            ps.setString(2, manager.getKeyAES());
            ps.setInt(3, utilisateur.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }
        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Satisfaction> satisfactions = null;
        try {
            satisfactions = mapSatisfaction(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }
        manager.fermerConnection();
        return satisfactions;
    }
    /**
     * 
     * @param rst
     * @return
     * @throws SQLException 
     */
    private List<Satisfaction> mapSatisfaction(ResultSet rst) throws SQLException {
        List<Satisfaction> satisfactions = new ArrayList<>();

        while (rst.next()) {

            Satisfaction satisfaction = new Satisfaction();
            satisfaction.setIdAppli(rst.getInt("idAppli"));
            satisfaction.setIdBase(rst.getInt("idBase"));
            satisfaction.setTauxSatisfaction(TauxSatisfaction.getByValeur(rst.getString("tauxSatisfaction")));
            satisfaction.setMessage(rst.getString("message"));
            satisfactions.add(satisfaction);
        }
        return satisfactions;
    }
    /**
     *
     * @param utilisateur
     * @return List<Client>
     * @throws DAOException
     */
    @Override
    public List<Client> getClientsMajParUtilisateur(Utilisateur utilisateur) throws DAOException {

        StringBuilder builder = new StringBuilder();
        builder.append("SELECT client.idBase, client.idAppli, ");
        builder.append("AES_DECRYPT(client.nom,?) AS nom ");
        builder.append("FROM client ");
        builder.append("JOIN utilisateur ON utilisateur.idAppli = client.idAppliUtilisateur ");
        builder.append("WHERE client.idAppliUtilisateur = ? ");
        builder.append("AND (client.idBase = 0 OR client.dateModification > utilisateur.dateSynchro)");

        String request = builder.toString();
        logger.debug("Select clients par utilisateur: {}", request);
        logger.debug("utilisateur {}", utilisateur);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement");
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setString(1, manager.getKeyAES());
            ps.setInt(2, utilisateur.getIdAppli());
            ps.executeQuery();

        } catch (SQLException e) {
            logger.error("Can not select data with id.");
            throw new DAOException("Can not select data with id.", e);
        }

        ResultSet rst;
        try {
            rst = ps.getResultSet();
        } catch (SQLException e) {
            logger.error("Can not get resultset.");
            throw new DAOException("Can not get resultset.", e);
        }

        List<Client> clients = null;
        try {
            clients = mapClient(rst);
        } catch (SQLException e) {
            logger.error("Can not populate map with result.", e);
            throw new DAOException("Can not populate map with result.", e);
        }

        if (null == clients || clients.isEmpty()) {
//            throw new NoResultException("No client found with utilisateur : " + utilisateur );
        }
        manager.fermerConnection();
        return clients;
    }

    /**
     *
     * @param rst
     * @return List<Client>
     * @throws SQLException
     */
    private List<Client> mapClient(ResultSet rst) throws SQLException {
        List<Client> clients = new ArrayList<>();

        while (rst.next()) {

            Client client = new Client();
            client.setIdAppli(rst.getInt("idAppli"));
            client.setIdBase(rst.getInt("idBase"));
            client.setNom(rst.getString("nom"));
            clients.add(client);
        }
        return clients;
    }

    /**
     *
     * @param client
     * @param utilisateur
     * @throws DAOException
     */
    @Override
    public void lierClientAUtilisateur(Client client, Utilisateur utilisateur) throws DAOException {

        if (null == utilisateur || null == client) {
            throw new IllegalArgumentException("Can not save a null object.");
        }

        StringBuilder builder = new StringBuilder();

        builder.append("UPDATE client ");
        builder.append("SET idAppliUtilisateur = ?,");
        builder.append("dateModification = NOW()");
        builder.append("WHERE idAppli = ?");

        String request = builder.toString();
        logger.debug("Lier client à utilisateur: {}", request);

        Connection con = manager.creerConnection();
        PreparedStatement ps;
        try {
            ps = con.prepareStatement(request);
        } catch (SQLException e) {
            logger.error("Can not prepare statement", e);
            throw new DAOException("Can not prepare statement.", e);
        }

        try {
            ps.setInt(1, utilisateur.getIdAppli());
            ps.setInt(2, client.getIdAppli());
            ps.executeUpdate();
        } catch (SQLException e) {
            logger.error("Can not link client to utilisateur.", e);
            throw new DAOException("Can not link client to utilisateur.", e);
        }
        manager.fermerConnection();
    }
}
